from flask import Flask, render_template, redirect, url_for
import mysql.connector
from mysql.connector.pooling import MySQLConnectionPool
from flask_socketio import SocketIO, emit
import threading
import time
import json
# 导入Decimal类型用于转换
from decimal import Decimal

app = Flask(__name__)
socketio = SocketIO(app)

# 配置 MySQL 连接信息
db_config = {
    'host': '192.168.229.150',
    'database': 'airport1',
    'user': 'root',
    'password': '1234567890'
}
# 模块1.未来七天运力预测数据查询
def get_capacity_forecast():
    connection = mysql.connector.connect(**db_config)
    cursor = connection.cursor()
    cursor.execute("""
        SELECT forecast_date, forecast_passenger, forecast_cargo 
        FROM capacity_forecast 
        ORDER BY forecast_date ASC
    """)
    data = cursor.fetchall()
    cursor.close()
    connection.close()

    processed_data = []
    for row in data:
        date = str(row[0]) if row[0] is not None else "未知日期"
        passenger = int(row[1]) if row[1] is not None else 0
        # 处理Decimal类型的货运量
        cargo = float(row[2]) if row[2] is not None else 0.0
        processed_data.append((date, passenger, cargo))

    return processed_data
# 模块2.不同类型机型每月流量统计
def execute_query(query):
    try:
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()
        cursor.execute(query)
        data = cursor.fetchall()
        cursor.close()
        connection.close()
        return data
    except mysql.connector.Error as err:
        print(f"数据库错误: {err}")
        return []
def get_air_data():
    months4 = list(range(1, 13))
    small_air_query = """
    SELECT
        MONTH(stats_month) AS OrderMonth,
        SUM(total_passenger + total_cargo) AS AirCount
    FROM
        aircraft_capacity_stats
    WHERE
        aircraft_level = 1
    GROUP BY
        stats_month
    ORDER BY
        stats_month ASC;
    """
    medium_air_query = """
    SELECT
        MONTH(stats_month) AS OrderMonth,
        SUM(total_passenger + total_cargo) AS AirCount
    FROM
        aircraft_capacity_stats
    WHERE
        aircraft_level = 2
    GROUP BY
        stats_month
    ORDER BY
        stats_month ASC;
    """
    large_air_query = """
    SELECT
        MONTH(stats_month) AS OrderMonth,
        SUM(total_passenger + total_cargo) AS AirCount
    FROM
        aircraft_capacity_stats
    WHERE
        aircraft_level = 3
    GROUP BY
        stats_month
    ORDER BY
        stats_month ASC;
    """

    small_air_data = execute_query(small_air_query)
    medium_air_data = execute_query(medium_air_query)
    large_air_data = execute_query(large_air_query)

    small_air_counts = [0] * 12
    medium_air_counts = [0] * 12
    large_air_counts = [0] * 12

    # 关键修复：将Decimal转换为float
    for month, count in small_air_data:
        if isinstance(count, Decimal):
            count = float(count)
        small_air_counts[month - 1] = count
    for month, count in medium_air_data:
        if isinstance(count, Decimal):
            count = float(count)
        medium_air_counts[month - 1] = count
    for month, count in large_air_data:
        if isinstance(count, Decimal):
            count = float(count)
        large_air_counts[month - 1] = count

    air_types = ['小型机', '中型机', '大型机']
    air_counts = [small_air_counts, medium_air_counts, large_air_counts]

    return months4, air_types, air_counts

# 模块3.航班类型统计
def execute_query_params(query, params=None):
    """带参数的查询执行函数（避免与模块2的execute_query冲突）"""
    try:
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()
        cursor.execute(query, params)
        data = cursor.fetchall()
        cursor.close()
        connection.close()
        return data
    except mysql.connector.Error as err:
        print(f"数据库错误: {err}")
        return []
    except Exception as e:
        print(f"其他错误: {e}")
        return []
def get_aircraft_data():
    """获取机型运力数据"""
    aircraft_levels = {
        1: '小型机',
        2: '中型机',
        3: '大型机'
    }

    query = """
    SELECT 
        aircraft_level,
        SUM(total_passenger) AS total_passenger,
        SUM(total_cargo) AS total_cargo
    FROM 
        aircraft_capacity_stats
    GROUP BY 
        aircraft_level
    ORDER BY 
        aircraft_level;
    """

    result = execute_query_params(query)

    aircraft_data = {
        '小型机': [0, 0],  # [客运量, 货运量]
        '中型机': [0, 0],
        '大型机': [0, 0]
    }

    # 关键修复：处理Decimal类型
    for level, passenger, cargo in result:
        if level in aircraft_levels:
            name = aircraft_levels[level]
            # 转换Decimal为int/float
            passenger_val = int(passenger) if passenger else 0
            if isinstance(passenger_val, Decimal):
                passenger_val = float(passenger_val)

            cargo_val = int(cargo) if cargo else 0
            if isinstance(cargo_val, Decimal):
                cargo_val = float(cargo_val)

            aircraft_data[name] = [passenger_val, cargo_val]

    types = ['小型机', '中型机', '大型机']
    counts = [aircraft_data[type] for type in types]
    return types, counts

# 模块六、机型匹配分析
def get_pie_chart_data():
    try:
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()
        cursor.execute("SELECT aircraft_level, total_count FROM aircraft_count_stats")
        data = cursor.fetchall()
        cursor.close()
        connection.close()

        # 关键修复：处理饼图数据中的Decimal
        pie_data = [{
            'name': str(row[0]) if row[0] is not None else "未知",
            'value': int(row[1]) if row[1] is not None else 0
        } for row in data]

        return pie_data
    except Exception as e:
        print(f"数据库查询错误: {e}")
        return []


# 模块9.近30天热门航线TOP5
def tollstation_fee():
    connection = mysql.connector.connect(**db_config)
    cursor = connection.cursor()
    cursor.execute(
        "SELECT end_airport, flight_count FROM popular_route_ranking ;")
    data = cursor.fetchall()
    cursor.close()
    connection.close()
    # 关键修复：确保flight_count不是Decimal
    processed_data = []
    for row in data:
        end_airport = str(row[0]) if row[0] is not None else "未知"
        flight_count = row[1] if row[1] is not None else 0.0
        # 转换Decimal为float
        if isinstance(flight_count, Decimal):
            flight_count = float(flight_count)
        processed_data.append((end_airport, flight_count))

    return processed_data
def background_thread():
    """定期查询数据库并推送更新给所有连接的客户端."""
    while True:
        socketio.sleep(3)  # 每隔3秒查询一次数据库
        # 模块1.未来七天运力预测数据查询
        forecast_data = get_capacity_forecast()
        dates = [row[0] for row in forecast_data]
        passengers = [row[1] for row in forecast_data]
        cargos = [row[2] for row in forecast_data]
        # 模块2.不同类型机型每月流量统计
        months4, air_types, air_counts = get_air_data()
        # 模块6、机型匹配分析
        data7 = get_pie_chart_data()
        print(data7)
        socketio.emit('update_chart7', data7)
        # 模块3.航班类型统计
        aircraft_types, aircraft_counts = get_aircraft_data()

        data9 = tollstation_fee()
        end_airport = [row[0] for row in data9]  # x
        flight_count = [row[1] for row in data9]  # y
        # 序列化前确保所有数据类型可JSON序列化
        payload = json.dumps({
            # 模块一：未来七天运力预测
            'dates': dates,
            'passengers': passengers,
            'cargos': cargos,
            # 模块二、不同类型机型每月流量统计
            'months4': months4,
            'air_types': air_types,
            'air_counts': air_counts,
            # 模块3.航班类型统计
            'aircraft_types': aircraft_types,
            'aircraft_counts': aircraft_counts,

            # 近30天热门航线TOP5
            'destination': end_airport,
            'total_fee': flight_count
        })
        print(payload)
        socketio.emit('update_chart', json.loads(payload))


@app.route('/port1')
def realtime_station_data():
    return render_template('port1.html')
@app.route('/port2')
def realtime_station_data1():
    return render_template('port2.html')
@app.route('/port3')
def realtime_station_data3():
    return render_template('port3.html')
@app.route('/realtime19')
def index():
    return render_template('realtime19.html')


@app.route('/airScreen')
def monthcars1():
    return render_template('airScreen.html')
if __name__ == '__main__':
    thread = socketio.start_background_task(background_thread)
    socketio.run(app, debug=True, allow_unsafe_werkzeug=True)